﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;
using Access = Microsoft.Office.Interop.Access;
using dao;
using System.Data;

namespace BesAsm.Framework.MsAccessUtilities
{
  /// <summary>
  /// Provides a wrapper class around an instance of Microsoft Access. This class
  /// should shield any calling classes from DAO calls, COM Interop and tracking the MS Access executable.
  /// Publicly accessible members within this class should not return any MS Access or DAO 
  /// objects, rather only primitives and .NET standard objects (List, DateTime, etc). This class
  /// is based on Office version 12.0 (Office 2003) and has not been tested on other versions.
  /// </summary>
  public class AccessHelper : IDisposable
  {
    private const int MSACCESS_MAX_FILENAME_LENGTH = 60;
    private Access.Application _accessApp;
    private bool _disposed;
    private DateTime _startTime;
    private dao.Database _currentDB;

    /// <summary>
    /// Creates a new Access database
    /// </summary>
    /// <param name="database">The full path including file name of the new database.</param>
    public static void CreateNewMdb(string database)
    {
      Assembly ass = Assembly.GetExecutingAssembly();
      System.IO.Stream stream = ass.GetManifestResourceStream("BesAsm.Framework.MsAccessUtilities._empty.mdb");
      System.IO.Stream output = new FileStream(database, FileMode.Create);

      byte[] buffer = new byte[32 * 1024];
      int read;

      while ((read = stream.Read(buffer, 0, buffer.Length)) > 0)
      {
        output.Write(buffer, 0, read);
      }

      output.Close();
    }

    /// <summary>
    /// Creates a new AccessHelper for manipulating the specified database.
    /// The database must be opened exclusively (this may be conservative,
    /// could refactor to allow shared access).
    /// </summary>
    /// <param name="databaseName">The fully qualifed path to an existing
    /// Access database.</param>
    public AccessHelper(string databaseName)
    {
      //force shutdown of access before we try to open a new instance of ms Access
      //This is necessary because if we are doing many many runs, eventually
      //access will fail to close completely (managed code doesn't always
      //implement a destructor completely).
      _startTime = System.DateTime.Now;
      _accessApp = ShellGetDB(databaseName, 2000);
      _currentDB = this._accessApp.CurrentDb();

      _disposed = false;
    }

    /// <summary>
    /// Links a single table in the specified Access database. The table will first be
    /// deleted if it already exists. The linked table will have the same name as the
    /// source table.
    /// </summary>
    /// <param name="tableName">The name of the table in the source database</param>
    /// <param name="sourceDatabase">The full path or connection string of a database containing the table to link</param>
    /// <param name="sourceDbType">Type of the source database</param>
    public void LinkTable(string tableName, string sourceDatabase, ExternalDatabaseType sourceDbType)
    {
      LinkTable(tableName, sourceDatabase, tableName, sourceDbType);
    }

    /// <summary>
    /// Links a single table in the specified Access database. The table will first be
    /// deleted if it already exists. The linked table will have the same name as the
    /// source table.
    /// </summary>
    /// <param name="tableName">The name of the table in the source database</param>
    /// <param name="sourceDatabase">The full path or connection string of a database containing the table to link</param>
    /// <param name="linkName">The new name of the linked table</param>
    /// <param name="sourceDbType">Type of the source database.</param>    
    /// <remarks>If sourceDbType is ExternalDatabaseType.SqlServer, then the sourceDatabase connection
    /// string syntax should be "Server=<i>ServerName</i>;Database=<i>DatabaseName</i>;Trusted_Connection=Yes"</remarks>
    public void LinkTable(string tableName, string sourceDatabase, string linkName, ExternalDatabaseType sourceDbType)
    {
      if (TableExists(linkName))
      {
        DeleteTable(linkName);
      }

      dao.TableDef linkTable;
      linkTable = _currentDB.CreateTableDef(linkName, Type.Missing, Type.Missing, Type.Missing);
      switch (sourceDbType)
      {
        case ExternalDatabaseType.MsAccess:
          linkTable.Connect = ";DATABASE=" + sourceDatabase;
          break;
        case ExternalDatabaseType.SqlServer:
          linkTable.Connect = "ODBC;Driver={SQL Server};" + sourceDatabase;
          break;
        default:
          throw new ArgumentException("Database type " + sourceDbType.ToString() + " not supported by LinkTable");
      }
      linkTable.SourceTableName = tableName;
      try
      {
        _currentDB.TableDefs.Append(linkTable);
      }
      catch (COMException ex)
      {
        if (ex.Message.Contains("Could not find installable ISAM."))
          throw new ArgumentException("Invalid connection string for linked SQL table", ex);
        else
          throw;
      }
      catch (Exception ex)
      {
        throw new AccessHelperException("Unable to link table '" + tableName + "'", ex);
      }

    }

    /// <summary>
    /// Copies the table schema from an existing table to a new empty table.
    /// </summary>
    /// <param name="existingTable">The existing table.</param>
    /// <param name="newTable">The new empty table to be created. Any existing table
    /// with the same name will be deleted.</param>    
    public void CopyTableSchema(string existingTable, string newTable)
    {
      if (TableExists(newTable))
        DeleteTable(newTable);

      TableDef existingTableDef = GetTableDef(existingTable);

      dao.TableDef newTableDef;
      newTableDef = _currentDB.CreateTableDef(newTable, Type.Missing, Type.Missing, Type.Missing);

      for (int i = 0; i < existingTableDef.Fields.Count; i++)
      {
        Field field = existingTableDef.Fields[i];
        int newFieldType = field.Type;
        if (newFieldType == (int)dao.DataTypeEnum.dbDecimal)
        {
          //HACK: Access field type 20 (decimal) causes problems when copying schema
          //for now, convert to type 7 (double )
          newFieldType = (int)dao.DataTypeEnum.dbDouble;

        }
        Field newField = newTableDef.CreateField(field.Name, newFieldType, field.Size);
        newField.Required = field.Required;
        //newField.AllowZeroLength = field.AllowZeroLength;
        //newField.DefaultValue = field.DefaultValue;
        newTableDef.Fields.Append(newField);
      }

      _currentDB.TableDefs.Append(newTableDef);

    }

    public void CopyTable(string existingTable, string newTable)
    {
      if (TableExists(newTable))
        DeleteTable(newTable);

      string sql = "SELECT " + existingTable +
        ".* INTO " + newTable +
        " FROM " + existingTable + ";";
      _currentDB.Execute(sql, Type.Missing);

      _currentDB.TableDefs.Refresh();
    }

    /// <summary>
    /// Checks to see if a table exists.
    /// </summary>
    /// <param name="databaseName">The full path to an Access Database</param>
    /// <param name="tableName">The table to check for existence</param>
    /// <returns>True is tableName exists in databaseName, otherwise false</returns>
    public bool TableExists(string tableName)
    {
      return GetTableDef(tableName) != null;
    }

    /// <summary>
    /// Deletes the specified table from the current database, if it exists.
    /// </summary>
    /// <param name="tableName">The name of the table to delete</param>
    public void DeleteTable(string tableName)
    {
      if (TableExists(tableName))
      {
        _currentDB.TableDefs.Delete(tableName);
      }
    }

    /// <summary>
    /// Executes a paremeterless macro within the current database.
    /// </summary>
    /// <param name="macroName">The name of the macro to execute</param>
    public void ExecuteMacro(string macroName)
    {
      _accessApp.DoCmd.RunMacro(macroName, Type.Missing, Type.Missing);
    }

    /// <summary>
    /// The name of a query to create in the current database.
    /// </summary>
    /// <param name="queryName">The name of the query to create</param>
    /// <param name="queryText">An SQL statement to store in a query in the 
    /// current database</param>
    public void CreateQuery(string queryName, string queryText)
    {
      DeleteQuery(queryName);
      _currentDB.CreateQueryDef(queryName, queryText);
    }

    /// <summary>
    /// Deletes a query from the current database.
    /// </summary>
    /// <param name="queryName">The name of the query to delete</param>
    public void DeleteQuery(string queryName)
    {
      if (GetQueryDef(queryName) != null)
      {
        _currentDB.QueryDefs.Delete(queryName);
      }
      return;
    }

    /// <summary>
    /// Executes an action query in the current database.
    /// </summary>
    /// <param name="queryName">The name of the action query to execute</param>
    public void ExecuteActionQuery(string queryName)
    {
      dao.QueryDef queryDef;
      queryDef = GetQueryDef(queryName);
      queryDef.Execute(null);
    }

    /// <summary>
    /// translates a CSV file to an access file for MapInfo consumption
    /// </summary>
    /// <param name="CSVFilePath">The full path to the CSV file</param>
    /// <param name="AccessOutputPath">The full path of the method's output</param>
    /// <param name="AccessOutputTableName">The intended name of the Access table to be created</param>
    public void ImportTable(string fileName, string tableName, FileType importType)
    {
      //Transfer the data from the csv file to the new Access Database
      //TransferText will have an exception for improper file names.
      if (IsBadFileName(fileName))
        throw new ArgumentException("Bad file name: " + fileName);
      else
        this._accessApp.DoCmd.TransferText(
          Microsoft.Office.Interop.Access.AcTextTransferType.acImportDelim,
          Type.Missing, tableName, fileName, true, Type.Missing, Type.Missing);
    }

    /// <summary>
    /// IsBadFileName should be used when importing a .csv file to
    /// access.  Access normally will have trouble with long file names.
    /// </summary>
    /// <param name="fileName">The name of the file (full path inclusive).</param>
    /// <returns>True for file names greater than 60 characters.  False otherwise.</returns>
    private bool IsBadFileName(string fileName)
    {
      string shortFileName;
      bool returnValue = false;

      shortFileName = System.IO.Path.GetFileName(fileName);
      if (shortFileName.Length > MSACCESS_MAX_FILENAME_LENGTH)
        returnValue = true;

      return returnValue;
    }

    /// <summary>
    /// AppendTable is a simple process for appending one access table to another
    /// access table.  Both tables must belong to the same database.
    /// </summary>
    /// <param name="sourceTable">The contents of the source table will be copied and added to the
    /// destination table.  The source table will remain fully populated after the process.</param>
    /// <param name="destinationTable">The destination table will retain the original values
    /// as well as a copy of the records in the sourceTable.  It is recommended that the
    /// function which calls AppendTable first checks that the two tables are compatible.</param>
    public void AppendTable(string sourceTable, string destinationTable)
    {
      _currentDB.Execute("INSERT INTO " + destinationTable + " SELECT * FROM " + sourceTable, Type.Missing);

      //CreateQuery("AppendTableQuery", );            
      //ExecuteActionQuery("AppendTableQuery");
      //DeleteQuery("AppendTableQuery");
      //DeleteTable("_" + tableName);
    }

    public void AppendIndex(
      string table,
      string indexName,
      bool isPrimary,
      bool isUnique,
      bool isRequired,
      bool ignoreNulls,
      List<string> fields)
    {

      try
      {
        TableDef tableDef = GetTableDef(table);
        Index newIndex = tableDef.CreateIndex(indexName);
        IndexFields newIndexFields = newIndex.Fields as dao.IndexFields;
        var distinctFields = fields.Distinct();
        foreach (string field in distinctFields)
        {
          Field indexField = newIndex.CreateField(field, Type.Missing, Type.Missing);
          newIndexFields.Append(indexField);
        }
        newIndex.Primary = isPrimary;
        newIndex.Unique = isPrimary ? true : isUnique;
        newIndex.Required = isPrimary ? true : isRequired;
        newIndex.IgnoreNulls = isPrimary ? false : ignoreNulls;
        tableDef.Indexes.Append(newIndex);
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }
    /// <summary>
    /// This function will check table schemas of two different tables to see if they match.
    /// The schemas will match only if both tables have the same number of fields, both 
    /// table's fields have the same names, and both table's fields use the same datatype.
    /// Some leeway may be required in fields such as TEXT, but those are special cases
    /// that should be accomodated and adjusted for before this function is called.
    /// </summary>
    /// <param name="tableA">Table A is one of two tables to check for schema compatibility.</param>
    /// <param name="tableB">Table B is one of two tables to check for schema compatibility.</param>
    /// <returns>Returns boolean fieldsMatch, which is false if the fields do not match, and 
    /// true if the fields do match.</returns>
    public bool SchemasMatch(string tableA, string tableB)
    {
      int namesMatch = 0;
      int numCols = 0;
      string fieldName = "";
      bool fieldsMatch = true;
      //number of fields must match
      if (_currentDB.TableDefs[tableA].Fields.Count == _currentDB.TableDefs[tableB].Fields.Count)
      {
        numCols = _currentDB.TableDefs[tableA].Fields.Count;
        //names of fields must match
        for (int i = 0; i < numCols; i++)
        {
          for (int j = 0; j < numCols; j++)
          {
            if (string.Compare(_currentDB.TableDefs[tableA].Fields[i].Name, _currentDB.TableDefs[tableB].Fields[j].Name) == 0)
            {
              namesMatch++;
            }
          }
        }

        if (namesMatch == numCols)
        {
          //Types of fields must match
          for (int i = 0; i < numCols; i++)
          {
            fieldName = _currentDB.TableDefs[tableA].Fields[i].Name;
            if (_currentDB.TableDefs[tableA].Fields[fieldName].Type.CompareTo(_currentDB.TableDefs[tableB].Fields[fieldName].Type) != 0)
            {
              fieldsMatch = false;
            }
          }
        }
        else
        {
          fieldsMatch = false;
        }
      }

      //types of fields must match
      return fieldsMatch;
    }

    /// <summary>
    /// Exports the contents of a query to an output file in the specified format. Note: Only
    /// .csv file type is implemented.
    /// </summary>
    /// <param name="queryName">The name of the query in the current database to export</param>
    /// <param name="outputFile">The file contents of the table should be exported to</param>
    /// <param name="exportType">The type of file to write to; .csv file is the only implemented
    /// type at this time, although .xml file should be implemented</param>
    /// <param name="append">If true, will append query results to outputFile (if outputFile already exists); 
    /// otherwise outputFile will be deleted.</param>
    public void ExportQuery(string queryName, string outputFile, FileType exportType, bool append)
    {
      StreamWriter streamWriter;
      string separator = GetFileTypeSeparator(exportType);

      try
      {
        if (!append && File.Exists(outputFile))
          File.Delete(outputFile);
        streamWriter = new StreamWriter(outputFile, append);
      }
      catch (Exception ex)
      {
        throw new AccessHelperException("Could not create file ' " + outputFile + "': " + ex.Message, ex);
      }

      try
      {
        dao.QueryDef queryDef;
        queryDef = GetQueryDef(queryName);
        if (queryDef == null)
        {
          throw new AccessHelperException("Query " + queryName + " not found");
        }
        dao.Recordset recordSet;
        recordSet = queryDef.OpenRecordset(Type.Missing, Type.Missing, Type.Missing);

        if (recordSet.EOF)
          return;

        recordSet.MoveFirst();
        while (!recordSet.EOF)
        {
          dao.Fields fields = recordSet.Fields;
          for (int i = 0; i <= fields.Count - 2; i++)
          {
            streamWriter.Write(fields[i].Value);
            streamWriter.Write(separator);
          }
          streamWriter.WriteLine(fields[fields.Count - 1].Value);
          recordSet.MoveNext();
        }
        recordSet = null;
        GC.Collect();
      }
      catch (Exception ex)
      {
        throw new AccessHelperException("Could not export query '" + queryName + "': " + ex.Message);
      }
      finally
      {
        streamWriter.Close();
      }
      return;
    }

    private string GetFileTypeSeparator(FileType exportType)
    {
      string separator;
      switch (exportType)
      {
        case FileType.CSV:
          separator = ", ";
          break;
        case FileType.Raw:
          separator = " ";
          break;
        default:
          throw new ArgumentException("FileType '" + exportType.ToString() + "' not implemented");
          break;
      }
      return separator;
    }

    /// <summary>
    /// Exports the contents of a table to an output file in the specified format. Note: Only 
    /// .csv file type is implemented.
    /// </summary>
    /// <param name="tableName">The name of the table in the current database to export</param>
    /// <param name="outputFile">The file contents of the table should be exported to</param>
    /// <param name="exportType">The type of file to write to; .csv file is the only implemented
    /// type at this time, although .xml file should be implemented</param>
    public void ExportTable(string tableName, string outputFile, FileType exportType)
    {
      _accessApp.DoCmd.TransferText(
        Microsoft.Office.Interop.Access.AcTextTransferType.acExportDelim,
        Type.Missing, tableName, outputFile, true, Type.Missing, Type.Missing);
      return;
    }

    /// <summary>
    /// Writes a value to a field in a key/value table.
    /// </summary>
    /// <param name="tableName">The name of the table to write to.</param>
    /// <param name="keyField">The field containing the key that indentifies the record to be modified</param>
    /// <param name="key">The key indicating the record to be updated</param>
    /// <param name="valueField">The field containing the value that should be modified</param>
    /// <param name="value">The new value to be placed in the value field</param>
    public void WriteKeyValueTable(string tableName, string keyField, string key, string valueField, object value)
    {
      dao.TableDef keyValueTable;
      keyValueTable = GetTableDef(tableName);
      dao.Recordset rs = keyValueTable.OpenRecordset(Type.Missing, Type.Missing);

      if (FindField(rs, keyField) == null)
      {
        throw new ArgumentException("UpdateKeyValueTable Exception: Key Field '" + keyField + "' not found.");
      }
      if (FindField(rs, valueField) == null)
      {
        throw new ArgumentException("UpdateKeyValueTable Exception: Value Field '" + valueField + "' not found.");
      }

      rs.MoveFirst();
      while (!rs.EOF)
      {
        if (((string)rs.Fields[keyField].Value).ToUpper() == key.ToUpper())
        {
          rs.Edit();
          rs.Fields[valueField].Value = value;
          rs.Update(1, false);
          return;
        }
        rs.MoveNext();
      }
      throw new ArgumentException("UpdateKeyValueTable Exception: Key '" + key + "' not found.");
    }

    /// <summary>
    /// Adds a field to an existing table.
    /// </summary>
    /// <param name="tableName">Name of the table.</param>
    /// <param name="fieldName">Name of the field.</param>
    /// <param name="fieldType">Type of the field.</param>
    public void AddField(string tableName, string fieldName, FieldType fieldType)
    {
      AddField(tableName, fieldName, fieldType, true);
    }

    /// <summary>
    /// Adds a field to an existing table.
    /// </summary>
    /// <param name="tableName">Name of the table.</param>
    /// <param name="fieldName">Name of the field.</param>
    /// <param name="fieldType">Type of the field.</param>
    /// <param name="ignoreIfExists">True to bypass field creation if it already exists</param>
    public void AddField(string tableName, string fieldName, FieldType fieldType, bool ignoreIfExists)
    {
      string fieldTypeToString = "";
      switch (fieldType)
      {
        case FieldType.String:
          fieldTypeToString = "TEXT(128)";
          break;
        case FieldType.Double:
          fieldTypeToString = "DOUBLE";
          break;
        case FieldType.Long:
          fieldTypeToString = "LONG";
          break;
        default:
          throw new AccessHelperException("Field type " + fieldType.ToString() + " not implemented");
      }
      //Check if the new field exists 
      if (FieldNameExists(tableName, fieldName, fieldType) == true)
      {
        if (!ignoreIfExists)
          throw new ArgumentException("Bad field name or type \n Name : " + fieldName + "\n Type : " + fieldTypeToString + "\n");
      }
      else
      {
        _currentDB.Execute("ALTER TABLE " + tableName + " ADD COLUMN " + fieldName + " " + fieldTypeToString + ";", Type.Missing);
      }
    }

    /// <summary>
    /// FieldNameExists is meant to be called before adding a new field to a 
    /// table.  This function will return true if the table already contains a
    /// field with the proposed field name.
    /// </summary>
    /// <param name="tableName">The table to check for the field name</param>
    /// <param name="fieldName">The name of the field to look for</param>
    /// <param name="fieldType">Unused.  Symmetry</param>
    /// <returns>Returns boolean true if the table already contains a field
    /// with the proposed field name, false otherwise.</returns>
    public bool FieldNameExists(string tableName, string fieldName, FieldType fieldType)
    {
      TableDef tbl;
      bool returnValue = false;

      tbl = GetTableDef(tableName);
      foreach (Field fld in tbl.Fields)
      {
        if (fld.Name == fieldName)
        {
          returnValue = true;
        }
      }

      return returnValue;
    }

    /// FieldNameExists is meant to be called before adding a new field to a 
    /// table.  This function will return true if the table already contains a
    /// field with the proposed field name.
    /// </summary>
    /// <param name="tableName">The table to check for the field name</param>
    /// <param name="fieldName">The name of the field to look for</param>
    /// <returns>Returns boolean true if the table already contains a field
    /// with the proposed field name, false otherwise.</returns>
    public bool FieldNameExists(string tableName, string fieldName)
    {
      // The third parameter is ignored anyway, so...
      return FieldNameExists(tableName, fieldName, FieldType.Double);
    }

    /// <summary>
    /// FieldTypeMatches checks to see if the field in question has a
    /// field type that is expected.
    /// </summary>
    /// <param name="tableName">The table to check for the field type</param>
    /// <param name="fieldName">The name of the field to check</param>
    /// <param name="fieldType">The type expected</param>
    /// <returns>Returns true if the field has a matching field type, false otherwise.</returns>
    public bool FieldTypeMatches(string tableName, string fieldName, FieldType fieldType)
    {
      TableDef tbl = GetTableDef(tableName);
      Field fld = tbl.Fields[fieldName];
      bool fieldTypeMatches = false;

      switch ((FieldType)fieldType)
      {
        case (FieldType.String):
          fieldTypeMatches = (dao.DataTypeEnum)fld.Type == dao.DataTypeEnum.dbText;
          break;
        default:
          throw new AccessHelperException("Field type " + fieldType.ToString() + " not implemented");
      }
      return fieldTypeMatches;
    }

    public void UpdateField(string tableName, string fieldName, object value)
    {
      if (value is System.String)
      {
        _currentDB.Execute("UPDATE " + tableName + " SET " + fieldName + " = '" + value.ToString() +
          "';", Type.Missing);
      }
      else
      {
        _currentDB.Execute("UPDATE " + tableName + " SET " + fieldName + " = " + value.ToString() +
          ";", Type.Missing);
      }
    }

    public void UpdateField(string tableName, string fieldName, string where, object value)
    {
      if (value is System.String)
      {
        _currentDB.Execute("UPDATE " + tableName + " SET " + fieldName + " = '" + value.ToString() +
          "' WHERE " + where + ";", Type.Missing);
      }
      else
      {
        _currentDB.Execute("UPDATE " + tableName + " SET " + fieldName + " = " + value.ToString() +
          " WHERE " + where + ";", Type.Missing);
      }
    }

    /// <summary>
    /// Updates data in a field.
    /// </summary>
    /// <param name="tableName">Name of the table containing the field to update.</param>
    /// <param name="keyFieldName">Name of the primary key field.</param>
    /// <param name="?">The name of the field to update.</param>
    /// <param name="data">The data.</param>
    public void UpdateField(string tableName, string keyFieldName, string updateFieldName, Dictionary<int, object> data)
    {
      string delimiter = "";
      foreach (KeyValuePair<int, object> kvp in data)
      {
        if (kvp.Value is string) delimiter = "'";

        _currentDB.ExecuteSQL(
          "UPDATE " + tableName +
          " SET " + updateFieldName + " = " + delimiter + kvp.Value + delimiter +
          " WHERE " + keyFieldName + " = " + kvp.Key + ";");
      }
    }

    public void InsertIntoTable(string tableName, DataTable data)
    {
      foreach (DataRow row in data.Rows)
      {
        string sql = "INSERT INTO " + tableName + " (";

        foreach (DataColumn column in data.Columns)
        {
          sql += column.ColumnName + ",";
        }
        sql = sql.TrimEnd(new char[] { ',' });
        sql += ")";

        sql += " " + "VALUES (";
        foreach (DataColumn column in data.Columns)
        {
          sql += row[column].ToString() + ",";
        }

        sql = sql.TrimEnd(new char[] { ',' });
        sql += ");";

        try
        {          
          _currentDB.Execute(sql, Type.Missing);
        }
        catch (Exception ex)
        {
          throw new Exception("Error inserting data to table '" + tableName + "'", ex);
        }

      }
    }

    /// <summary>
    /// Executes an aggregate query which returns a single row.
    /// </summary>
    /// <param name="queryName">Name of the query.</param>
    /// <returns>A Dictionary containing key-value-pairs of the field names and field results</returns>
    public Dictionary<string, object> ExecuteAggregateQuery(string queryName)
    {
      dao.QueryDef queryDef;
      queryDef = this.GetQueryDef(queryName);
      Dictionary<string, object> aggregateQueryResults;
      aggregateQueryResults = new Dictionary<string, object>();

      dao.Recordset recordSet;
      recordSet = queryDef.OpenRecordset(Type.Missing, Type.Missing, Type.Missing);

      dao.Fields fields = recordSet.Fields;
      for (int i = 0; i < fields.Count; i++)
      {
        aggregateQueryResults.Add(fields[i].Name, fields[i].Value);
      }
      return aggregateQueryResults;
    }

    public Dictionary<string, double> ExecuteAggregateQueryDoubles(string queryName)
    {
      dao.QueryDef queryDef;
      queryDef = this.GetQueryDef(queryName);
      Dictionary<string, double> aggregateQueryResults;
      aggregateQueryResults = new Dictionary<string, double>();

      dao.Recordset recordSet;
      recordSet = queryDef.OpenRecordset(Type.Missing, Type.Missing, Type.Missing);

      dao.Fields fields = recordSet.Fields;
      for (int i = 0; i < fields.Count; i++)
      {
        if (!Convert.IsDBNull(fields[i].Value))
        {
          aggregateQueryResults.Add(fields[i].Name, (double)fields[i].Value);
        }
        else
        {
          aggregateQueryResults.Add(fields[i].Name, (double)0);
        }

      }
      return aggregateQueryResults;
    }

    /// <summary>
    /// Exports multiple queries to an output file. The queries to export are obtained from a pre-existing MS Access
    /// table containing the names of the queries and the order to export them.
    /// </summary>
    /// <param name="queryTableName">Name of the table containing the queries to export.</param>
    /// <param name="queryFieldName">Name of a text field in the query table identifying the names of the queries to export.</param>
    /// <param name="sortFieldName">Name of a numeric field which determines the order in which queries are exported.</param>
    /// <param name="outputFile">The output file to export to.</param>
    /// <param name="fileType">Type of the file to export.</param>
    public void ExportQueryTable(string queryTableName, string queryFieldName, string sortFieldName, string outputFile, FileType fileType)
    {
      string separator = GetFileTypeSeparator(fileType);

      const string TEMP_QUERY_NAME = "TEMP_QUERY_TABLE";
      string tempQuerySelect =
        "SELECT [" + queryTableName + "].[" + queryFieldName + "], [" + queryTableName + "].[" + sortFieldName + "] " +
        "FROM " + queryTableName + " ORDER BY " + sortFieldName + " ASC";

      CreateQuery(TEMP_QUERY_NAME, tempQuerySelect);

      QueryDef queryTable = GetQueryDef(TEMP_QUERY_NAME);

      dao.Recordset rs = queryTable.OpenRecordset(Type.Missing, Type.Missing, Type.Missing);
      rs.MoveFirst();
      while (!rs.EOF)
      {
        string query = (string)rs.Fields[queryFieldName].Value;
        ExportQuery(query, outputFile, FileType.Raw, true);
        rs.MoveNext();
      }
      return;
    }

    /// <summary>
    /// Executes multiple action queries. The queries to execute are obtained from a pre-existing MS Access table
    /// containing the names of the queries and the order to execute them. This method will attempt to execute all
    /// queries in the query table, even if one or more queries fails.
    /// </summary>
    /// <param name="queryTableName">Name of the table containing the queries to execute.</param>
    /// <param name="queryFieldName">Name of a text field in the query table identifying the names of the queries to execute.</param>
    /// <param name="sortFieldName">Name of a numeric field which determines the order in which queries are executed.</param>
    /// <returns>A collection of strings indicating the names any queries which failed to execute.</returns>
    public List<string> ExecuteQueryTable(string queryTableName, string queryFieldName, string sortFieldName)
    {
      List<string> failedQueries = new List<string>();
      const string TEMP_QUERY_NAME = "TEMP_QUERY_TABLE";
      string tempQuerySelect =
        "SELECT [" + queryTableName + "].[" + queryFieldName + "], [" + queryTableName + "].[" + sortFieldName + "] " +
        "FROM " + queryTableName + " ORDER BY " + sortFieldName + " ASC";

      CreateQuery(TEMP_QUERY_NAME, tempQuerySelect);

      QueryDef queryTable = GetQueryDef(TEMP_QUERY_NAME);

      dao.Recordset rs = queryTable.OpenRecordset(Type.Missing, Type.Missing, Type.Missing);
      rs.MoveFirst();
      while (!rs.EOF)
      {
        string query = (string)rs.Fields[queryFieldName].Value;

        try
        {
          ExecuteActionQuery(query);
        }
        catch (Exception ex)
        {
          failedQueries.Add(query);
        }
        rs.MoveNext();
      }
      return failedQueries;
    }

    #region DAO helper methods

    /// <summary>
    /// Returns a TableDef that matches the provided table name.
    /// </summary>
    /// <param name="tableName">The name of the table to find</param>
    /// <returns>A TableDef in the current database matching table name,
    /// or null if no table is found</returns>
    private dao.TableDef GetTableDef(string tableName)
    {
      _currentDB.TableDefs.Refresh();

      dao.TableDefs tableDefs = _currentDB.TableDefs;

      for (int i = 0; i < tableDefs.Count; i++)
      {
        dao.TableDef tableDef = tableDefs[i];
        if (tableDef.Name.ToUpper() == tableName.ToUpper())
        {
          return tableDef;
        }
      }
      return null;
    }

    /// <summary>
    /// Returns a QueryDef that matches the provided query name.
    /// </summary>
    /// <param name="queryName">The name of the query to find</param>
    /// <returns>A QueryDef in the current database matching query name,
    /// or null if no query is found</returns>
    private dao.QueryDef GetQueryDef(string queryName)
    {
      dao.QueryDefs queryDefs = _currentDB.QueryDefs;
      queryDefs.Refresh();

      for (int i = 0; i < queryDefs.Count; i++)
      {
        dao.QueryDef queryDef = queryDefs[i];
        if (queryName.ToUpper() == queryDef.Name.ToUpper())
        {
          return queryDef;
        }
      }
      return null;
    }

    /// <summary>
    /// Returns a Field in the specified Recordset matching the provided field name.
    /// </summary>
    /// <param name="rs">The Recordset containing the field to be located</param>
    /// <param name="fieldName">The name of the field to be found</param>
    /// <returns>The field in the provided recordset that matches the provided field name, or null if the
    /// field is not found</returns>
    private dao.Field FindField(Recordset rs, string fieldName)
    {
      rs.MoveFirst();
      for (int i = 0; i < rs.Fields.Count; i++)
      {
        dao.Field field;
        field = rs.Fields[i];
        if (field.Name.ToUpper() == fieldName.ToUpper())
        {
          return field;
        }
      }
      return null;
    }

    public void RefreshLinkedTable(string databasePath, string tableName, string sourceTableName)
    {
      TableDef table = _currentDB.TableDefs[tableName];
      table.Connect = string.Format(";DATABASE={0};TABLE={1}", databasePath, sourceTableName);
      table.RefreshLink();
    }
    #endregion

    #region IDisposable Members
    /// <summary>
    /// Releases unmanaged resources (particularly, will kill MSAccess.exe).
    /// Should be called manually since GC does not occur deterministically.
    /// </summary>
    public void Dispose()
    {
      if (this._disposed)
      {
        return;
      }

      string ldbFile = Path.Combine(
        Path.GetDirectoryName(_currentDB.Name),
        Path.GetFileNameWithoutExtension(_currentDB.Name) + ".ldb");

      try
      {
        if (_accessApp.CurrentDb() != null)
        {
          _accessApp.CurrentDb().Close();
        }
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_accessApp.CurrentDb());
      }
      catch { }

      try
      {
        //HACK: Access does not get disposed of properly - After GarbageCollection has
        //occured, MSACCESS.EXE continues to run independently of the process that created it.
        //The following code finds the Access Process and kills it.
        System.Diagnostics.Process[] proc;
        System.Diagnostics.Process killProc = null;
        proc = System.Diagnostics.Process.GetProcesses();

        TimeSpan deltaStart = System.TimeSpan.MaxValue;
        foreach (System.Diagnostics.Process p in proc)
        {
          int mainWindowHandle;
          mainWindowHandle = (int)p.MainWindowHandle;
          if (p.ProcessName == "MSACCESS")
          {
            TimeSpan newDeltaStart;
            newDeltaStart = _startTime.Subtract(p.StartTime);

            if (Math.Abs(newDeltaStart.Ticks) < Math.Abs(deltaStart.Ticks))
            {
              deltaStart = newDeltaStart;
              killProc = p;
            }
          }
        }
        if (Math.Abs(deltaStart.TotalSeconds) < 5 && killProc != null)
        {
          killProc.Kill();
        }
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_accessApp);

        if (File.Exists(ldbFile))
          File.Delete(ldbFile);
      }
      catch { }

      _accessApp = null;
      this._disposed = true;

    }

    /// <summary>
    /// Destructor, called automatically when this instance is Garbage Collected
    /// </summary>
    ~AccessHelper()
    {
      //Release unmanaged resources
      this.Dispose();
    }

    #endregion

    #region Code taken from: "How to automate Microsoft Access by using Visual C#" http://support.microsoft.com/kb/317114
    private Access.Application ShellGetDB(string sDBPath, int iSleepTime)
    {
      //Launches a new instance of Access with a database (sDBPath)
      //using System.Diagnostics.Process.Start. Then, returns the
      //Application object via calling: BindToMoniker(sDBPath). Returns
      //the Application object of the new instance of Access, assuming that
      //sDBPath is not already opened in another instance of Access. To
      //ensure the Application object of the new instance is returned, make
      //sure sDBPath is not already opened in another instance of Access
      //before calling this function.
      // 
      //Example:
      //Access.Application oAccess = null;
      //oAccess = ShellGetDB("c:\\mydb.mdb", null,
      //  ProcessWindowStyle.Minimized, 1000);

      Access.Application oAccess = null;
      string sAccPath = null; //path to msaccess.exe
      string sCmdLine = null;
      System.Diagnostics.Process p = null;
      System.Diagnostics.ProcessWindowStyle enumWindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;

      // Enable exception handler:
      try
      {
        // Obtain the path to msaccess.exe:
        sAccPath = GetOfficeAppPath("Access.Application", "msaccess.exe");
        if (sAccPath == null)
        {
          throw new Exception("AccessHelper can't determine path to msaccess.exe");
        }

        // Make sure specified database (sDBPath) exists:
        if (!System.IO.File.Exists(sDBPath))
        {
          throw new Exception("AccessHelper can't find the file '" + sDBPath + "'");
        }

        // Start a new instance of Access passing sDBPath and sCmdLine:
        if (sCmdLine == null)
          sCmdLine = @"""" + sDBPath + @"""";
        else
          sCmdLine = @"""" + sDBPath + @"""" + " " + sCmdLine;
        System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
        startInfo.FileName = sAccPath;
        startInfo.Arguments = sCmdLine;
        startInfo.WindowStyle = enumWindowStyle;
        p = System.Diagnostics.Process.Start(startInfo);
        p.WaitForInputIdle(60000); //max 1 minute wait for idle input state

        // Move focus back to this form. This ensures that Access
        // registers itself in the ROT:
        //this.Activate();

        // Pause before trying to get Application object:
        System.Threading.Thread.Sleep(iSleepTime);

        // Obtain Application object of the instance of Access
        // that has the database open:
        oAccess = (Access.Application)System.Runtime.InteropServices.Marshal.BindToMoniker(sDBPath);
        return oAccess;
      }
      catch (Exception ex)
      {
        // Try to quit Access due to an unexpected error:
        try // use try..catch in case oAccess is not set
        {
          oAccess.Quit(Access.AcQuitOption.acQuitSaveNone);
        }
        finally
        {
          oAccess = null;
          throw ex;
        }
      }
    }
    private string GetOfficeAppPath(string sProgId, string sEXE)
    {
      //Returns path of the Office application. e.g.
      //GetOfficeAppPath("Access.Application", "msaccess.exe") returns
      //full path to Microsoft Access. Approach based on Q240794.
      //Returns null if path not found in registry.

      // Enable exception handler:
      try
      {
        Microsoft.Win32.RegistryKey oReg =
           Microsoft.Win32.Registry.LocalMachine;
        Microsoft.Win32.RegistryKey oKey = null;
        string sCLSID = null;
        string sPath = null;
        int iPos = 0;

        // First, get the clsid from the progid from the registry key
        // HKEY_LOCAL_MACHINE\Software\Classes\<PROGID>\CLSID:
        oKey = oReg.OpenSubKey(@"Software\Classes\" + sProgId + @"\CLSID");
        sCLSID = oKey.GetValue("").ToString();
        oKey.Close();

        // Now that we have the CLSID, locate the server path at
        // HKEY_LOCAL_MACHINE\Software\Classes\CLSID\ 
        // {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx}\LocalServer32:
        oKey = oReg.OpenSubKey(@"Software\Classes\CLSID\" + sCLSID +
           @"\LocalServer32");
        sPath = oKey.GetValue("").ToString();
        oKey.Close();

        // Remove any characters beyond the exe name:
        iPos = sPath.ToUpper().IndexOf(sEXE.ToUpper()); // 0-based position
        sPath = sPath.Substring(0, iPos + sEXE.Length);
        return sPath.Trim();
      }
      catch
      {
        return null;
      }
    }
    #endregion

  }

  /// <summary>
  /// Enumerated type indicating file format to use when exporting or importing data.
  /// When adding new Types to this enum, please assign an integer value.
  /// </summary>
  public enum FileType
  {
    /// <summary>
    /// Comma-separated value file
    /// </summary>
    CSV = 1,
    /// <summary>
    /// Raw text file with no formatting
    /// </summary>
    Raw
  }
  /// <summary>
  /// Enumerated type indicating field format for access, csv, excel, etc.
  /// When adding new Types to this enum, please assign an integer value.
  /// </summary>
  public enum FieldType
  {
    //String should apply to text, string or char types.  Access
    //will differentiate between the three, but excel and csv
    //will simply consider them all strings.
    String = 1,
    Double,
    Long
  }

  /// <summary>
  /// Specifies the type of an external database for import, export, and link table operations
  /// </summary>
  public enum ExternalDatabaseType
  {
    /// <summary>
    /// An MS Access external database whilc will accessed via the full path to the database
    /// </summary>
    MsAccess,
    /// <summary>
    /// A SQL Server external database which will be accessed via a connection string
    /// </summary>
    SqlServer
  }
}
